package com.aier.school.service.impl;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import net.eicp.ghostboy.futil.MD5Util;
import net.eicp.ghostboy.futil.RandomUtil;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import com.aier.classes.common.InviteRequestUtil;
import com.aier.classes.entity.ClassParent;
import com.aier.classes.entity.Classes;
import com.aier.classes.service.IClassService;
import com.aier.classes.service.IClassUserService;
import com.aier.common.common.Constant;
import com.aier.common.dao.ICommonDAO;
import com.aier.common.service.ISysPhoneMessageService;
import com.aier.school.common.SchoolQuickUserUtil;
import com.aier.school.entity.DailyFood;
import com.aier.school.entity.School;
import com.aier.school.entity.SchoolGrade;
import com.aier.school.entity.SchoolGradeAttendance;
import com.aier.school.entity.SchoolIcCard;
import com.aier.school.entity.SchoolInfoDetail;
import com.aier.school.entity.SchoolMessage;
import com.aier.school.entity.SchoolPhoto;
import com.aier.school.entity.SchoolTeacher;
import com.aier.school.entity.SchoolTeacherCharisma;
import com.aier.school.entity.mapper.DailyFoodRowMapper;
import com.aier.school.entity.mapper.SchoolGradeAttendanceRowMapper;
import com.aier.school.entity.mapper.SchoolGradeRowMapper;
import com.aier.school.entity.mapper.SchoolInfoDetailRowMapper;
import com.aier.school.entity.mapper.SchoolMessageRowMapper;
import com.aier.school.entity.mapper.SchoolPhotoRowMapper;
import com.aier.school.entity.mapper.SchoolRowMapper;
import com.aier.school.entity.mapper.SchoolTeacherCharismaRowMapper;
import com.aier.school.entity.mapper.SchoolTeacherRowMapper;
import com.aier.school.service.ISchoolIcCardService;
import com.aier.school.service.ISchoolService;
import com.aier.user.entity.User;
import com.aier.user.entity.mapper.UserPhotoImgRowMapper;
import com.aier.user.service.IUserService;

@Component("schoolService")
public class SchoolServiceImpl_J implements ISchoolService {

    @Resource(name = "commonDao")
    private ICommonDAO commonDao;

    @Resource(name = "schoolIcCardService")
    private ISchoolIcCardService schoolIcCardService;

    @Resource(name = "userService")
    private IUserService userService;

    @Resource(name = "classUserService")
    private IClassUserService classUserService;

    @Resource(name = "sysPhoneMessageService")
    private ISysPhoneMessageService sysPhoneMessageService;

    @Resource(name = "schoolService")
    private ISchoolService schoolService;

    @Resource(name = "classService")
    private IClassService classService;

    public void setCommonDao(ICommonDAO commonDao) {
        this.commonDao = commonDao;
    }

    public void setSchoolIcCardService(ISchoolIcCardService schoolIcCardService) {
        this.schoolIcCardService = schoolIcCardService;
    }

    public void setUserService(IUserService userService) {
        this.userService = userService;
    }

    public void setClassUserService(IClassUserService classUserService) {
        this.classUserService = classUserService;
    }

    public void setSysPhoneMessageService(
            ISysPhoneMessageService sysPhoneMessageService) {
        this.sysPhoneMessageService = sysPhoneMessageService;
    }

    public void setSchoolService(ISchoolService schoolService) {
        this.schoolService = schoolService;
    }

    public void setClassService(IClassService classService) {
        this.classService = classService;
    }

    @Override
    public SchoolInfoDetail searchSchoolInfoBySid(Integer sid) {
        return (SchoolInfoDetail) commonDao
                .listByObject(
                        "select * from school_info where sid = ?",
                        new Object[] { sid }, new SchoolInfoDetailRowMapper());

    }

    @Override
    public int countSchoolByName(String sname, Integer sid) {
        String sql = " from school where name=? ";
        if (sid != null) {
            sql = sql + " and sid!=" + sid;
        }
        return commonDao.countByQuali(sql, new Object[] { sname });
    }

    @Override
    public School searchSchoolByCid(Long cid) {
        return (School) commonDao
                .listByObject(
                        "select * from school where sid=(select sid from classes where cid=?)",
                        new Object[] { cid }, new SchoolRowMapper());
    }

    @Override
    public School searchSchoolBySid(Integer sid) {
        return (School) commonDao
                .listByObject("select s.*,count(st.tid) as num_teacher," +
                		"(select count(cp.cpid) from class_parent cp INNER JOIN classes c on cp.cid=c.cid where c.sid=s.sid) as num_parent,"
                        + "(select count(cs.csid) from class_student cs INNER JOIN classes c on cs.cid=c.cid where c.sid=s.sid) as num_student "
                        + "from school s LEFT JOIN school_teacher st ON s.sid=st.sid where s.sid=? GROUP BY sid",
                        new Object[] { sid }, new SchoolRowMapper(
                                SchoolRowMapper.AND_USERNUMS));
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<School> searchSchoolListByUid(Long uid) {
        return (List<School>) commonDao
                .list("select * from `school` where sid in (select sid from school_teacher where uid=? "
                        + "UNION select c.sid from classes c INNER JOIN class_parent cp on c.cid=cp.cid where cp.uid=?);",
                        new Object[] { uid, uid }, new SchoolRowMapper());
    }
    
    public School searchSchoolByUid(Long uid){
    	String sql="select * from school where sid = ( select sid from school_teacher where  uid=? ) ";
    	School s=(School) commonDao.listByObject(sql,  new Object[] { uid }, new SchoolRowMapper());
		return s;
    }

    @Override
    public int insertSchool(School school) {
        KeyHolder kh = commonDao.insert(
                SchoolRowMapper.getPreparedStatementCreator(school),
                new GeneratedKeyHolder());
        if (kh != null) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int insertSchoolNew(School school) {
        KeyHolder kh = commonDao.insert(
                SchoolRowMapper.getPreparedStatementCreator(school),
                new GeneratedKeyHolder());
        if (kh != null) {
            return kh.getKey().intValue();
        } else {
            return 0;
        }
    }

    @Override
    public int deleteSchool(Integer sid) {
        if (commonDao.update("delete from school where sid=?",
                new Object[] { sid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int updateSchoolLogo(Integer sid, String slogo) {
        if (commonDao.update("update school set slogo=? where sid=?",
                new Object[] { slogo, sid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int updateSchoolImg(Integer sid, String img) {
        if (commonDao.update("update school set img=? where sid=?",
                new Object[] { img, sid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int updateSchoolInfo(School school) {
        if (commonDao.update(
                "update school set name=?,desinfo=? where sid=?",
                new Object[] { school.getName(), school.getDesinfo(),
                        school.getSid() }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }
    
    @Override
    public int updateMessageSuffix(Integer sid, String mesSuffix) {
        if (commonDao.update(
                "update school set mesSuffix=? where sid=?",
                new Object[] { mesSuffix,sid}) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    // =====================================================================================

    @Override
    public Map<String, Object> searchSchoolPhotoList(int page, int pagesize,
            Integer sid) {
        String sql = " from school_photo where sid=? order by spid desc";
        String sql_first = sql;
        return commonDao.queryPage("select * " + sql, "select count(*) "
                + sql_first, page, pagesize, new Object[] { sid },
                new Object[] { sid }, new UserPhotoImgRowMapper());
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<SchoolPhoto> searchSchoolPhotoList(Integer sid) {
        return (List<SchoolPhoto>) commonDao.list("select sp.*,if(sp.img = s.slogo,1,0) as flag from school_photo sp left join school s " +
        		"on sp.sid = s.sid where sp.sid=? order by flag desc, spid desc", 
                    new Object[] { sid },new SchoolPhotoRowMapper());
    }

    @Override
    public long insertSchoolPhoto(SchoolPhoto sp) {
        KeyHolder kh = commonDao.insert(
                SchoolPhotoRowMapper.getPreparedStatementCreator(sp),
                new GeneratedKeyHolder());
        if (kh != null) {
            return kh.getKey().longValue();
        } else {
            return 0;
        }
    }

    @Override
    public int updateSchoolPhoto(SchoolPhoto sp) {
        if (commonDao.update(
                "update school_photo set img=?,desinfo=? where spid=?",
                new Object[] { sp.getImg(), sp.getDesinfo(), sp.getSpid() }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int deleteSchoolPhoto(Long spid) {
        if (commonDao.update("delete from school_photo where spid=?",
                new Object[] { spid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    // =====================================================================================

    @Override
    public Map<String, Object> searchSchoolGradeList(int page, int pagesize,
            Integer sid) {
        String sql = " from school_grade as sg where sg.sid=? ";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "select sg.*,(select count(*) from school_teacher where gid=sg.gid) as typeNumber "
                                + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { sid }, new Object[] { sid },
                        new SchoolGradeRowMapper(
                                SchoolGradeRowMapper.AND_NUMBER));
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<SchoolGrade> searchSchoolGradeList(Integer sid) {
        return (List<SchoolGrade>) commonDao.list(
                "select sg.* from school_grade as sg where sg.sid=? order by sg.gid ",
                new Object[] { sid }, new SchoolGradeRowMapper());
    }

    @Override
    public SchoolGrade searchSchoolGradeByCondition(long gid) {
        SchoolGrade sg = (SchoolGrade) commonDao.listByObject(
                "select * from school_grade where gid=? ",
                new Object[] { gid }, new SchoolGradeRowMapper());
        if (sg != null) {
            sg.setSgaList(searchSchoolGradeAttendanceList(gid));
        }
        return sg;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<SchoolGradeAttendance> searchSchoolGradeAttendanceList(Long gid) {
        return (List<SchoolGradeAttendance>) commonDao
                .list("select * from  school_grade_attendance as sga right join school_grade as sg "
                        + "on sga.gid=sg.gid where sga.gid=? order by sga.type;",
                        new Object[] { gid },
                        new SchoolGradeAttendanceRowMapper());
    }

    @Override
    public int checkSchoolGradeByName(String gname, int sid, Long gid) {
        String sql = "from school_grade where name=? and sid=? ";
        if (gid != null) {
            sql = sql + " and gid!=" + gid;
        }
        int grade_num = commonDao
                .countByQuali(sql, new Object[] { gname, sid });
        return grade_num;
    }

    @Override
    public int insertSchoolGrade(int sid, String gname, String desinfo,
            String[] agdates, Time sadate, Time eadate, Time spdate, Time epdate) {
        int returnInt = 0;
        SchoolGrade sg = new SchoolGrade(sid, gname, desinfo);
        KeyHolder kh = commonDao.insert(
                SchoolGradeRowMapper.getPreparedStatementCreator(sg),
                new GeneratedKeyHolder());
        if (kh != null) {
            long gid = kh.getKey().longValue();
            if (sadate != null && epdate != null) {
                for (int i = 0; i < agdates.length; i++) {
                    Short type = Short.valueOf(agdates[i]);
                    SchoolGradeAttendance sga = new SchoolGradeAttendance(gid,
                            type, sadate, eadate, spdate, epdate);
                    KeyHolder kh2 = commonDao.insert(
                            SchoolGradeAttendanceRowMapper
                                    .getPreparedStatementCreator(sga),
                            new GeneratedKeyHolder());
                    if (kh2 == null) {
                        break;
                    }
                }
            }
            returnInt = 1;
        }

        return returnInt;
    }

    @Override
    public int deleteSchoolGrade(long gid) {
        if (commonDao.update("delete from school_grade where gid=?",
                new Object[] { gid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int updateSchoolGrade(long gid, String gname, String desinfo,
            String[] agdates, Time sadate, Time eadate, Time spdate, Time epdate) {
        int returnInt = 0;
        if (commonDao.update(
                "update school_grade set name=?,desinfo=? where gid=?",
                new Object[] { gname, desinfo, gid }) > 0) {
            for (int i = 0; i < agdates.length; i++) {
                Short type = Short.valueOf(agdates[i]);
                commonDao
                        .update("delete from school_grade_attendance where gid=? and type=?",
                                new Object[] { gid, type });
                if (sadate == null && eadate == null && spdate == null
                        && epdate == null) {
                    continue;
                } else {
                    SchoolGradeAttendance sga = new SchoolGradeAttendance(gid,
                            type, sadate, eadate, spdate, epdate);
                    KeyHolder kh2 = commonDao.insert(
                            SchoolGradeAttendanceRowMapper
                                    .getPreparedStatementCreator(sga),
                            new GeneratedKeyHolder());
                    if (kh2 == null) {
                        break;
                    }
                }
            }
            returnInt = 1;
        }
        return returnInt;
    }

    // =====================================================================================

    @Override
    public Map<String, Object> searchSchoolMessageList(int page, int pagesize,
            Integer sid) {
        String sql = "from school_message as sm left join school_teacher st on sm.tid=st.tid where sm.sid=? and (type=? or type=?) " +
        		"order by sm.ctime desc";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "select sm.*,(select headimg from user where uid=st.uid) as headimg_teacher,(st.tname) as name_teacher," +
                        "(select GROUP_CONCAT(DISTINCT(cp.pname)) from school_phonemessage_history sph left join class_parent cp " +
                        "on sph.uid = cp.uid " +
                        "where type = 1 and relativeid = sm.mid) as successStr,(select GROUP_CONCAT(DISTINCT(cp.pname)) from " +
                        "phonemessage_sendError_history psh left join class_parent cp on psh.uid = cp.uid where type = 1 and " +
                        "relativeid = sm.mid) as failStr " + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { sid,SCHOOL_MESSAGE_TYPE_SCHOOL_TS,SCHOOL_MESSAGE_TYPE_SCHOOL_T }, 
                        new Object[] {sid, SCHOOL_MESSAGE_TYPE_SCHOOL_TS,SCHOOL_MESSAGE_TYPE_SCHOOL_T },
                        new SchoolMessageRowMapper(
                                SchoolMessageRowMapper.AND_TEACHERNAME));
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<SchoolMessage> searchSchoolMessageList(Integer sid) {
        return (List<SchoolMessage>) commonDao
                .list("select sm.* from school_message as sm where sm.sid=? and (type=? or type = ?) order by sm.ctime desc  ",
                        new Object[] { sid, SCHOOL_MESSAGE_TYPE_SCHOOL_TS,SCHOOL_MESSAGE_TYPE_SCHOOL_T },
                        new SchoolMessageRowMapper());
    }

    @Override
    public Map<String, Object> searchClassMessageList(int page, int pagesize,
            Long cid) {
        String sql = "from school_message as sm left join school_teacher st on sm.tid=st.tid where "
                + "(sm.cid=? or sm.sid=(select sid from classes where cid=?) ) order by sm.ctime desc ";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "select sm.*,(select headimg from user where uid=st.uid) as headimg_teacher,(st.tname) as name_teacher,"
                        +"(select GROUP_CONCAT(DISTINCT(cp.pname)) from school_phonemessage_history sph left join class_parent cp " +
                        "on sph.uid = cp.uid " +
                        "where type = 1 and relativeid = sm.mid and cp.cid = ?) as successStr,(select GROUP_CONCAT(DISTINCT(cp.pname)) from " +
                        "phonemessage_sendError_history psh left join class_parent cp on psh.uid = cp.uid where type = 1 and " +
                        "relativeid = sm.mid and cp.cid = ?) as failStr "+ sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { cid, cid,cid,cid }, new Object[] {cid,cid }, new SchoolMessageRowMapper(
                                                SchoolMessageRowMapper.AND_TEACHERNAME));
    }

    @Override
    public long insertSchoolMessage(int sid, Long tid, String message,
            String imageName) {
        SchoolMessage sm = new SchoolMessage(sid, null, tid, message,
                SCHOOL_MESSAGE_TYPE_SCHOOL_TS, imageName, null,null);
        Timestamp ctime = new Timestamp(new Date().getTime());
        sm.setCtime(ctime);
        KeyHolder kh = commonDao.insert(
                SchoolMessageRowMapper.getPreparedStatementCreator(sm),
                new GeneratedKeyHolder());
        return kh.getKey().longValue();
    }

    @Override
    public long insertClassMessage(Long cid, Long tid, String message,
            String imageName) {
        SchoolMessage sm = new SchoolMessage(null, cid, tid, message,
                SCHOOL_MESSAGE_TYPE_CLASS, imageName, null,null);
        Timestamp ctime = new Timestamp(new Date().getTime());
        sm.setCtime(ctime);
        KeyHolder kh = commonDao.insert(
                SchoolMessageRowMapper.getPreparedStatementCreator(sm),
                new GeneratedKeyHolder());
        return kh.getKey().longValue();
    }

    // =====================================================================================

    /**
     * 添加每日餐饮
     * @return
     */
    @Override
    public int insertDailyFood(Integer sid,String dailyfood,Short ismessage) {
        Timestamp ctime = new Timestamp(new Date().getTime());
        for (String df:dailyfood.split("@")) {
            DailyFood dailyFood = (DailyFood) commonDao.listByObject("select * from dailyfood where ddate = ? and sid =?", 
                    new Object[]{df.split("\\$")[0],sid},new DailyFoodRowMapper());
            if (dailyFood != null) {
                return Constant.num_10001;
            }
            DailyFood sd = new DailyFood();
            sd.setCdate(ctime);
            sd.setSid(sid);
            sd.setDdate(df.split("\\$")[0]);
            sd.setContent(df.split("\\$")[1]);
            sd.setState((short)0);
            sd.setIsmessage(ismessage);
            commonDao.insert(
                    DailyFoodRowMapper.getPreparedStatementCreator(sd),
                    new GeneratedKeyHolder());
        }
        
        return 1;
    }
    
    /**
     * 删除每日餐饮
     * @return
     */
    @Override
    public int deleteDailyFood(Long dfid) {
        return commonDao.update("delete from dailyfood where dfid = ?",new Object[]{dfid});
    }
    
    /**
     * 修改每日餐饮
     * @return
     */
    @Override
    public int updateDailyFood(Long dfid,String ddate,String content) {
        Timestamp ctime = new Timestamp(new Date().getTime());
        DailyFood df = (DailyFood) commonDao.listByObject("select * from dailyfood where dfid = ?", new Object[]{dfid},new DailyFoodRowMapper());
        if (df.getDdate().equals(ddate)) {
            commonDao.update("update dailyfood set content = ?,cdate = ? where dfid = ?",new Object[]{content,ctime,dfid});
        } else {
            // 查询此天是否已存在餐饮信息
            DailyFood dailyFood = (DailyFood) commonDao.listByObject("select * from dailyfood where ddate = ?", 
                    new Object[]{ddate},new DailyFoodRowMapper());
            if (dailyFood != null) {
                return Constant.num_10001;
            }
            commonDao.update("update dailyfood set content = ?,cdate = ?,ddate=? where dfid = ?",new Object[]{content,ctime,ddate,dfid});
        }
        return 1;
    }
    
    /**
     * 添加每日餐饮
     * @return
     */
    @Override
    public int insertDailyFoodImg(Long dfid,String picName) {
        Timestamp ctime = new Timestamp(new Date().getTime());
        for (String img:picName.split(",")) {
            commonDao.update("insert into dailyfood_img(dfid,img,cdate) values(?,?,?)",new Object[]{dfid,img,ctime});
        }
        
        return 1;
    }

    /**
     * 查询餐饮信息
     * @param date
     * @return
     */
    @Override
    public Map<String, Object> searchDailyFood(int page,int page_size,Integer sid) {
        String sql = "select df.*,(select group_concat(img) from dailyfood_img where dfid = df.dfid) as img from dailyfood df " +
                "where df.sid = ? order by df.ddate desc";
        String sql_count = "select count(*) from dailyfood df where df.sid = ? order by df.ddate desc";
        return commonDao.queryPage(sql,sql_count,
                page, page_size, new Object[]{sid}, new Object[]{sid},new DailyFoodRowMapper());
    }


    /**
     * 查询代理商所创建的学校信息
     * 
     * @param pid
     *            代理商id
     * @return
     */
    @Override
    public Map<String, Object> serachSchoolInfoBypid(Integer pid, int page,
            int page_size) {
        if (null == pid) {
            return null;
        }
        StringBuffer sql = new StringBuffer(
                "select s.*,(select group_concat(CONCAT(st.tname,':',u.account)) FROM school_teacher st "
                        + "LEFT JOIN user u on st.uid=u.uid where st.sid=s.sid and isadmin=1) as managers from school s where s.pid=?");
        List<Object> lo = new ArrayList<Object>();
        lo.add(pid);

        return commonDao.queryPage(sql.toString(), page, page_size, lo,
                new SchoolRowMapper(SchoolRowMapper.AND_MANAGERS));

    }

    @Override
    public Map<String, Object> searchSchoolList(int page, int page_size,
            Integer cid, String sname) {
        String sql = "select s.*,(select count(*) from classes where sid = s.sid) as classcount  from school s";
        List<Object> largcount = new ArrayList<Object>();
        if (null != sname && !"".equals(sname)) {
            sql += " where s.`name` like ?";
            largcount.add("%" + sname + "%");
            if (null != cid && cid != -1) {
                sql += " and FIND_IN_SET(s.cid, getChildLst(?))";
                largcount.add(cid);
            }
        } else {
            if (null != cid && cid != -1) {
                sql += " where FIND_IN_SET(s.cid, getChildLst(?))";
                largcount.add(cid);
            }
        }
        return commonDao.queryPage(sql, page, page_size, largcount,
                new SchoolRowMapper());
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<School> searchSchoolByCid(Integer cid) {
        return (List<School>) commonDao
                .list("select * from school s where FIND_IN_SET(s.cid, getChildLst(?))",
                        new Object[] { cid }, new SchoolRowMapper());
    }

    @Override
    public int insertQuickSchool(File file, String fileContentType, Integer sid) {
        String error_info = "";
        Timestamp ctime = new Timestamp(new Date().getTime());
        Map<String, List<List<String>>> mapExcle;
        try {
            mapExcle = SchoolQuickUserUtil.parseExcel(file, fileContentType);
            if (mapExcle == null) {
                throw new RuntimeException("导入模板内容为空");
            }
        } catch (Exception e1) {
            throw new RuntimeException("解析导入模板出错，请检查模板数据是否正确");
        }
        List<List<String>> schoolTeacherRowList = mapExcle
                .get(SchoolQuickUserUtil.SCHOOL_TEACHER);
        List<List<String>> schoolParentRowList = mapExcle
                .get(SchoolQuickUserUtil.SCHOOL_PARENT);

        // 卡号map
        List<SchoolIcCard> schoolIcCardList = schoolIcCardService
                .searchSchoolCardList(sid);
        Map<String, Long> schoolIcCardNumMap = new HashMap<String, Long>();
        for (SchoolIcCard sic : schoolIcCardList) {
            schoolIcCardNumMap.put(sic.getCardnum(), sic.getIcid());
        }
        // 班级map
        List<Classes> classList = classService.searchClassList(sid);
        Map<String, Long> classMap = new HashMap<String, Long>();
        for (Classes c : classList) {
            classMap.put(c.getCname(), c.getCid());
        }

        // 检查基础数据
        error_info = checkExcelNormal(schoolTeacherRowList, schoolParentRowList);
        if (!"".equals(error_info)) {
            throw new RuntimeException(error_info);
        }
        // 检查数据库数据
        error_info = checkExcelDatabase(schoolParentRowList, sid, classMap);
        if (!"".equals(error_info)) {
            throw new RuntimeException(error_info);
        }
        
//        // 新增检查表格中数据是否与系统中已有数据重复（同班级同名）(2014-02-19)
//        for (List<String> rowList : schoolParentRowList) {
//            String className = rowList.get(0);
//            String babyName = rowList.get(1);
//            Long cid = classMap.get(className);
//            List<ClassStudent> csList = classUserService.searchCSByCidName(cid, babyName);
//            if (null != csList && csList.size() > 0) {
//                return 2;
//            }
//        }

        School school = schoolService.searchSchoolBySid(sid);
        // 创建教师
        for (List<String> rowList : schoolTeacherRowList) {// "教师姓名(必填)","手机号(必填)"
            String tname = rowList.get(0);
            String phone = rowList.get(1);
            String pwd = "";
            // 没有教师账号则新建一个账号,短信内容有所不同
            User user_teacher = userService.searchUserByCondition(phone);
            if (user_teacher == null) {
                pwd = RandomUtil.genRandomNum(Constant.num_6);
                user_teacher = new User(phone, MD5Util.getMD5(pwd), tname,
                        phone);
                userService.insertUserNormal(user_teacher);
                user_teacher = userService.searchUserByCondition(phone);
            }
            SchoolTeacher schoolTeacher = (SchoolTeacher) commonDao
                    .listByObject(
                            "select * from school_teacher where uid=? and sid=?",
                            new Object[] { user_teacher.getUid(), sid },
                            new SchoolTeacherRowMapper());
            if (schoolTeacher == null) {
                schoolTeacher = new SchoolTeacher(null, user_teacher.getUid(),
                        sid, tname, null, ctime, null);
                KeyHolder kh = commonDao.insert(SchoolTeacherRowMapper
                        .getPreparedStatementCreator(schoolTeacher),
                        new GeneratedKeyHolder());
                schoolTeacher.setTid(kh.getKey().longValue());
            }
            // 发送回执短信
            String phMessage = InviteRequestUtil.returnIRTeacherMessage(
                    phone, school.getName(), null, pwd);
            //sysPhoneMessageService.insertSendPhoneMessage(phone, phMessage);
            sysPhoneMessageService.insertSchoolPhoneMessageMobile(phone, phMessage,null,null,0,null);
        }

        // 创建家长 学生 绑定卡(班级)
        for (List<String> rowList : schoolParentRowList) {// 所在班级(必填)","宝宝姓名(必填)","监护人和宝宝的关系(必填)","监护人手机号(必填)","IC卡1","IC卡2","IC卡3"
            String className = rowList.get(0);
            String babyName = rowList.get(1);
            String relation = rowList.get(2);
            String phone = rowList.get(Constant.num_3);
            String cardNum1 = rowList.get(Constant.num_4);
            String cardNum2 = rowList.get(Constant.num_5);
            String cardNum3 = rowList.get(Constant.num_6);
            String pwd = "";
            String parentName = babyName + relation;

            Long cid = classMap.get(className);
            // 没有家长账号则新建一个账号,短信内容有所不同
            User user_parent = userService.searchUserByCondition(phone);
            if (user_parent == null) {
                pwd = RandomUtil.genRandomNum(Constant.num_6);
                user_parent = new User(phone, MD5Util.getMD5(pwd), parentName,
                        phone);
                userService.insertUserNormal(user_parent);
                user_parent = userService.searchUserByCondition(phone);
            }
//            User baby_user = userService.insertUserBabyByCondition(
//                    user_parent.getUid(), babyName, relation);// 创建宝宝账号
            Long csid = userService.insertUserBabyByConditionNew(
                  user_parent.getUid(), babyName, relation,cid);// 创建宝宝账号
//            ClassStudent classStudent = new ClassStudent(null,
//                    baby_user.getUid(), babyName, cid, ctime);
//            KeyHolder kh = commonDao.insert(ClassStudentRowMapper
//                    .getPreparedStatementCreator(classStudent),
//                    new GeneratedKeyHolder());// 创建班级学生
//            Long csid = kh.getKey().longValue();
            List<ClassParent> cp = classUserService.searchCPByCondition(user_parent.getUid(), cid, csid);
            if (null == cp || cp.size() == 0) {
                ClassParent classParent = new ClassParent(null,
                        user_parent.getUid(), cid, parentName, csid, ctime,
                        relation);
                classUserService.insertClassParent(classParent);// 创建班级家长
            }
            
            if (cardNum1 != null && !"".equals(cardNum1)) {
                Long icid = schoolIcCardNumMap.get(cardNum1);
                schoolIcCardService.updateBindCardStudent(sid, icid, csid);// 绑ic卡1
            }
            if (cardNum2 != null && !"".equals(cardNum2)) {
                Long icid = schoolIcCardNumMap.get(cardNum2);
                schoolIcCardService.updateBindCardStudent(sid, icid, csid);// 绑ic卡2
            }
            if (cardNum3 != null && !"".equals(cardNum3)) {
                Long icid = schoolIcCardNumMap.get(cardNum3);
                schoolIcCardService.updateBindCardStudent(sid, icid, csid);// 绑ic卡3
            }
            // 发送短信
            String phMessage = InviteRequestUtil.returnIRParentMessage(
                    phone, babyName, className, school.getName(), pwd);
            //sysPhoneMessageService.insertSendPhoneMessage(phone, phMessage);
            sysPhoneMessageService.insertSchoolPhoneMessageMobile(phone, phMessage,null,null,0,null);

        }
        return 1;
    }

    // 检查导入的Excel的基础数据
    private static String checkExcelNormal(
            List<List<String>> schoolTeacherRowList,
            List<List<String>> schoolParentRowList) {
        String temp_str;

        temp_str = SchoolQuickUserUtil.checkRepeat(schoolTeacherRowList, 1);
        if (!"".equals(temp_str)) {
            return "教师手机号重复：" + temp_str;
        }
        
//        temp_str = SchoolQuickUserUtil.checkRepeat(schoolParentRowList,
//                Constant.num_3);
//        if (!"".equals(temp_str)) {
//            return "监护人手机号重复：" + temp_str;
//        }
        List<Integer> cardRowList = new ArrayList<Integer>();
        cardRowList.add(Constant.num_4);
        cardRowList.add(Constant.num_5);
        cardRowList.add(Constant.num_6);
        temp_str = SchoolQuickUserUtil.checkRepeatMore(schoolParentRowList,
                cardRowList);
        if (!"".equals(temp_str)) {
            return "IC卡号重复：" + temp_str;
        }
        
        // 新增检查同班中是否有同名学生（2014-02-19）
        temp_str = SchoolQuickUserUtil.checkRepeatTwo(schoolParentRowList, 0, 1);
        if (!"".equals(temp_str)) {
            return temp_str.split(",")[0] + "学生名重复：" + temp_str.split(",")[1];
        }
        
        return "";
    }

    // 检查导入的Excel的数据库数据
    private String checkExcelDatabase(List<List<String>> schoolParentRowList,
            int sid, Map<String, Long> classMap) {
        String cardNum, className;
        List<String> schoolIcCardNumList = schoolIcCardService
                .searchSchoolCardNumList(sid);// 学校卡号列表
        List<String> schoolIcCardNumList_Bind = schoolIcCardService
                .searchSchoolBindCardNumList(sid);// 学校已经分派的卡号列表
        for (int i = 0; i < schoolParentRowList.size(); i++) {
            for (int j = Constant.num_4; j < Constant.num_7; j++) {
                cardNum = schoolParentRowList.get(i).get(j);
                if (null != cardNum && !"".equals(cardNum)) {
                    if (schoolIcCardNumList.indexOf(cardNum) == -1) {
                        return cardNum + "不是该学校的卡";
                    }
                    if (schoolIcCardNumList_Bind.indexOf(cardNum) != -1) {
                        return cardNum + "已经被绑定了";
                    }
                }
            }
            className = schoolParentRowList.get(i).get(0);
            if (classMap.get(className) == null) {
                return className + "不存在,请先创建好班级";
            }
        }

        return "";
    }
    
    @Override
    public int insertQuickSchool(File file, String fileContentType) {
        String error_info = "";
        Timestamp ctime = new Timestamp(new Date().getTime());
        Map<String, List<List<String>>> mapExcle;
        try {
            mapExcle = SchoolQuickUserUtil.parseExcel(file, fileContentType);
            if (mapExcle == null) {
                throw new RuntimeException("导入模板内容为空");
            }
        } catch (Exception e1) {
            throw new RuntimeException("解析导入模板出错，请检查模板数据是否正确");
        }
        List<List<String>> schoolRowList = mapExcle
                .get("学校");
        // 创建学校
        for (List<String> rowList : schoolRowList) {// "教师姓名(必填)","手机号(必填)"
            String tname = rowList.get(0);
            String phone = rowList.get(1);
            
        }
        return 1;
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<School> searchAllSchoolList() {
        return (List<School>) commonDao
                .list("select * from `school`",
                        new Object[] { }, new SchoolRowMapper());
    }
    
    @Override
    public Map<String, Object> searchSchoolMessageListNew(int page, int pagesize,
            Integer sid) {
        String sql = "from school_message as sm left join school_teacher st on sm.tid=st.tid where sm.sid=? and (type=? or type=?) " +
                "order by sm.ctime desc";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "select sm.*,st.tname as name_teacher " + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { sid,SCHOOL_MESSAGE_TYPE_SCHOOL_TS,SCHOOL_MESSAGE_TYPE_SCHOOL_T }, 
                        new Object[] {sid, SCHOOL_MESSAGE_TYPE_SCHOOL_TS,SCHOOL_MESSAGE_TYPE_SCHOOL_T },
                        new SchoolMessageRowMapper(
                                SchoolMessageRowMapper.AND_TEACHER));
    }

    @Override
    public Map<String, Object> searchClassMessageListNew(int page, int pagesize,
            Long cid) {
        String sql = "from school_message as sm left join school_teacher st on sm.tid=st.tid where "
                + "(sm.cid=? or sm.sid=(select sid from classes where cid=?) ) order by sm.ctime desc ";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "select sm.*,(st.tname) as name_teacher "+ sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { cid, cid }, new Object[] {cid,cid }, new SchoolMessageRowMapper(
                                                SchoolMessageRowMapper.AND_TEACHER));
    }
    
    @Override
    public Map<String, Object> searchParentMessageListNew(int page,
            int pagesize, Long cid) {
        String sql = "from school_message as sm left join school_teacher st on sm.tid=st.tid where "
                + "(sm.cid=? or (sm.sid=(select sid from classes where cid=?) and sm.type = 0)) order by sm.ctime desc";
        String sql_first = sql;
        return commonDao
                .queryPage(
                        "select sm.*,(st.tname) as name_teacher "+ sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { cid, cid }, new Object[] {cid,cid }, new SchoolMessageRowMapper(
                                                SchoolMessageRowMapper.AND_TEACHER));
    }
    
    @Override
    public long insertSchoolMessageNew(int sid, Long tid, String title, String message,
            String imageName, Short type, Integer count) {
        SchoolMessage sm = new SchoolMessage(sid, null, tid, message,
                type, imageName, title, count);
        Timestamp ctime = new Timestamp(new Date().getTime());
        sm.setCtime(ctime);
        KeyHolder kh = commonDao.insert(
                SchoolMessageRowMapper.getPreparedStatementCreator(sm),
                new GeneratedKeyHolder());
        return kh.getKey().longValue();
    }

    @Override
    public long insertClassMessageNew(Long cid, Long tid, String title, String message,
            String imageName,Integer count) {
        SchoolMessage sm = new SchoolMessage(null, cid, tid, message,
                SCHOOL_MESSAGE_TYPE_CLASS, imageName, title,count);
        Timestamp ctime = new Timestamp(new Date().getTime());
        sm.setCtime(ctime);
        KeyHolder kh = commonDao.insert(
                SchoolMessageRowMapper.getPreparedStatementCreator(sm),
                new GeneratedKeyHolder());
        return kh.getKey().longValue();
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    @Override
    public int insertMessageReading(Long mid,Long tid, Long uid) {
        if (tid != null) {
            // 教师阅读
            String count = (String)commonDao.listByObject("select count(*) as count from school_message_read where mid = ? and tid = ?", 
                    new Object[]{mid,tid},
                    new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                        return rs.getString("count");
                    }
                }
            );
            if ("0".equals(count)) {
                SchoolMessage sm = (SchoolMessage)commonDao.listByObject("select * from school_message where mid = ?", 
                        new Object[]{mid},new SchoolMessageRowMapper());
                // 班级公告的阅读量跟教师无关
                if (sm.getType() != 1) {
                    commonDao.update("update school_message set reading = IFNULL(reading,0)+1 where mid=?",
                            new Object[] { mid});
                    commonDao.update("insert into school_message_read(mid,tid) values(?,?)",new Object[] {mid,tid});
                }
                
            }
        } else {
            // 家长阅读
            SchoolMessage sm = (SchoolMessage)commonDao.listByObject("select * from school_message where mid = ?", 
                    new Object[]{mid},new SchoolMessageRowMapper());
            List<ClassParent> cpList = new ArrayList<ClassParent>();
            // 全校师生的公告（type = 0）
            if (sm.getType() == 0) {
                cpList = (List<ClassParent>) commonDao.list("select cp.* from class_parent cp left join " +
                        "classes c on cp.cid = c.cid where c.sid = ? and cp.uid = ?",new Object[]{sm.getSid(),uid});
                
            } else {
                // 班级公告（type = 1）
                cpList = (List<ClassParent>) commonDao.list("select cp.* from class_parent cp where cp.cid = ? and cp.uid = ?",
                        new Object[]{sm.getCid(),uid});
                
            }
            String count = (String)commonDao.listByObject("select count(*) as count from school_message_read where mid = ? and cpid = ?", 
                    new Object[]{mid,((Map)cpList.get(0)).get("cpid")},
                    new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                        return rs.getString("count");
                    }
                }
            );
            if ("0".equals(count)) {
                commonDao.update("update school_message set reading = IFNULL(reading,0)+? where mid=?",
                        new Object[] {cpList.size(), mid});
                for(int i=0;i<cpList.size();i++) {
                    commonDao.update("insert into school_message_read(mid,csid,cpid) values(?,?,?)",
                            new Object[] {mid,((Map)cpList.get(i)).get("csid"),((Map)cpList.get(i)).get("cpid")});
                }
            }
        }
        return 1;
    }
    

    @Override
    public String searchUnReadingList(Long mid) {
        String snameList = (String)commonDao.listByObject("select  group_concat(cs.sname) as snameList from class_student cs " +
        		"left join school_message sm on cs.cid = sm.cid where cs.csid not in (select csid from school_message_read smr " +
        		"where smr.mid = ?) and sm.mid = ?", 
                new Object[]{mid,mid},
                new RowMapper<String>() {
                @Override
                public String mapRow(ResultSet rs, int arg1)
                    throws SQLException {
                    return rs.getString("snameList");
                }
            }
        );
        return snameList;
    }

    @Override
    public int updateSchoolDesinfo(int sid,String desinfo) {
        if (null != desinfo & !"".equals(desinfo)) {
            return commonDao.update("update school set desinfo = ? where sid = ?",new Object[]{desinfo,sid});
        } else {
            return 0;
        }
        
    }

    @Override
    public int updateTeacherCharisma(Long stcid, String name, String img,
            String desinfo) {
        return commonDao.update("update school_teacher_charisma set name = ?, img = ?, desinfo = ? where stcid = ?",
                new Object[]{name,img,desinfo,stcid});
    }

    @Override
    public int deleteTeacherCharisma(Long stcid) {
        return commonDao.update("delete from school_teacher_charisma where stcid = ?",new Object[]{stcid});
    }

    @Override
    public int insertTeacherCharisma(Integer sid,String img, String name,String desinfo) {
        int s = 1;
        Timestamp ctime = new Timestamp(new Date().getTime());
                s = commonDao.update("insert into school_teacher_charisma(sid,img,name,desinfo,ctime) values (?,?,?,?,?)",
                        new Object[]{sid,img,name,desinfo,ctime});
                if(s==0){
                    return 0;
                }
            
        
        return s;
    }

    @Override
    public int updateSchoolInfo(Integer sid, String phone, String address,
            String url, String postcode,String email,String fax ) {
        SchoolInfoDetail si = (SchoolInfoDetail) commonDao.listByObject("select * from school_info where sid = ?", 
                new Object[]{sid},new SchoolInfoDetailRowMapper());
        if (si == null) {
            return commonDao.update("insert into school_info(sid,address,phone,url,postcode,email,fax) values (?,?,?,?,?,?,?)",
                    new Object[]{sid,address,phone,url,postcode,email,fax});
        } else {
            return commonDao.update("update school_info set address=?,phone=?,url=?,postcode=? ,email=? ,fax=?  where sid = ?",
                    new Object[]{address,phone,url,postcode,email,fax,sid});
        }
        
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<SchoolTeacherCharisma> searchSTCList(Integer sid) {
        return (List<SchoolTeacherCharisma>) commonDao.list("select * from school_teacher_charisma where sid = ?",
                new Object[]{sid},new SchoolTeacherCharismaRowMapper());
    }

	@Override
	public SchoolMessage searchMessagebyMid(Long mid) {		
		 SchoolMessage sm = (SchoolMessage)commonDao.listByObject("select * from school_message where mid = ?", 
                 new Object[]{mid},new SchoolMessageRowMapper());
		return sm;
	}

	@Override
	 public int insertTeacherCharisma(Integer sid, String charisma) {
        int s = 1;
        Timestamp ctime = new Timestamp(new Date().getTime());
        for(String c:charisma.split("@")){
            if(!"".equals(c)){
                String[] strList = c.split(",");
                String img = strList[0];
                String name = strList[1];
                String desinfo = strList[2];
                s = commonDao.update("insert into school_teacher_charisma(sid,img,name,desinfo,ctime) values (?,?,?,?,?)",
                        new Object[]{sid,img,name,desinfo,ctime});
                if(s==0){
                    return 0;
                }
            }
        }
        return s;
    }
}
